import sqlite3
from datetime import datetime

# 封装获取数据库连接函数
def get_connection():
    return sqlite3.connect('config.db')

# 查询函数：根据图片名或时间范围查询
def query_defects(image_keyword=None, start_time=None, end_time=None):
    conn = get_connection()
    try:
        sql = "SELECT * FROM defects WHERE 1=1"
        params = []

        if image_keyword:
            sql += " AND image_path LIKE ?"
            params.append(f"%{image_keyword}%")
        
        if start_time:
            sql += " AND detected_at >= ?"
            params.append(start_time)

        if end_time:
            sql += " AND detected_at <= ?"
            params.append(end_time)

        cursor = conn.execute(sql, params)
        results = cursor.fetchall()
        return results
    finally:
        conn.close()


# 示例调用
if __name__ == "__main__":
    print("查询缺陷记录：")
    
    # 示例1：按图片关键词查询
    results = query_defects(image_keyword="uploads/crazing_2.jpg")
    
    # 示例2：按时间段查询
    # results = query_defects(start_time="2025-07-02 00:00:00", end_time="2025-07-02 23:59:59")

    for r in results:
        print(r)
